package com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiHouSur.repository.impl;

import com.css.fxfzxczh.base.attachment.service.AttachmentInfoService;
import com.css.fxfzxczh.base.dict.repository.entity.DictEntity;
import com.css.fxfzxczh.base.dict.repository.entity.DictItemEntity;
import com.css.fxfzxczh.common.utils.UpdateUtil;
import com.css.fxfzxczh.constants.YNEnum;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiData.entity.SamPoiData;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiHouSur.entity.SamPoiHouSur;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiHouSur.queryParams.SamPoiHouSurQuaParams;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiHouSur.repository.SamPoliHouSurRepository;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiHouSur.vo.ResultParamVo;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiHouSur.vo.SamPoiHouSurCheckVo;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiHouSur.vo.SamPoiHouSurVo;
import com.css.fxfzxczh.modules.fieldWorkManage.surTaskManage.entity.SurTaskManage;
import com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.vo.ResultVo;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.*;

@Repository
public class SamPoliHouSurRepositoryImpl implements SamPoliHouSurRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    AttachmentInfoService attachmentInfoService;


    @Override
    public Map<String, Object> getPage(SamPoiHouSurQuaParams queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from xczh_sampling_point_house_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and check_status!='0' ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getIntensityDetermination())) {
            whereSql.append(" and intensity_determination = '" + queParams.getIntensityDetermination() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointHouseCode())) {
            whereSql.append(" and sampling_point_house_code like '%" + queParams.getSamplingPointHouseCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCheckStatus())) {
            whereSql.append(" and check_status =  '" + queParams.getCheckStatus() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamPoiHouSur.class)).size();
        List<SamPoiHouSur> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SamPoiHouSur.class));

        List<SamPoiHouSurVo> samPoiHouSurVoList=new ArrayList<>();
        for(SamPoiHouSur item:list){
            SamPoiHouSurVo samPoiHouSurVo=new SamPoiHouSurVo();
            UpdateUtil.copyNullProperties(item, samPoiHouSurVo);
            if (PlatformObjectUtils.isNotEmpty(item.getBeforeEarthquakePicture())){
                samPoiHouSurVo.setBeforeEarthquakePicture(attachmentInfoService.queryFile(item.getBeforeEarthquakePicture()));
            }
            if (PlatformObjectUtils.isNotEmpty(item.getAfterEarthquakePicture())){
                samPoiHouSurVo.setAfterEarthquakePicture(attachmentInfoService.queryFile(item.getAfterEarthquakePicture()));
            }
            samPoiHouSurVoList.add(samPoiHouSurVo);
        }
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", samPoiHouSurVoList);
        return map;
    }

    @Override
    public boolean save(SamPoiHouSur samPoiHouSur) {
        String sql="insert into xczh_sampling_point_house_data(id,xsem_id,sampling_point_house_code,province,city,area,address,longitude,latitude,sampling_point_households," +
                "per_household_area,sampling_point_population,house_typical_damage,death_toll,disappear_toll,serious_injury_toll,minor_wound_toll,topographic_features,personnel_shock," +
                "reaction_of_vessels,macro_description,large_scale_photo,large_scale_video,macroscopic_anomaly,geologic_hazard,building_earthquake_description,earthquake_description," +
                "intensity_determination,disaster_description,before_earthquake_picture,after_earthquake_picture,uav_aerial_photography_one,uav_aerial_photography_two,enclosure_photo," +
                "create_user,create_time,update_user,update_time,del_flag,geom,create_user_name,xspd_id,sampling_point_name,enclosure_video,enclosure_audio,enclosure_album,check_status," +
                "check_opinion,is_damnification_count,repeat_data_code,type) " +
                "values(:id, :xsemId, :samplingPointHouseCode, :province, :city, :area, :address, :longitude, :latitude, :samplingPointHouseholds, :perHouseholdArea, " +
                ":samplingPointPopulation, :houseTypicalDamage, :deathToll, :disappearToll, :seriousInjuryToll, :minorWoundToll, :topographicFeatures, :personnelShock, " +
                ":reactionOfVessels, :macroDescription, :largeScalePhoto, :largeScaleVideo, :macroscopicAnomaly, :geologicHazard, :buildingEarthquakeDescription, :earthquakeDescription, " +
                ":intensityDetermination, :disasterDescription, :beforeEarthquakePicture, :afterEarthquakePicture, :uavAerialPhotographyOne, :uavAerialPhotographyTwo, :enclosurePhoto, " +
                ":createUser, :createTime,:updateUser, :updateTime, :delFlag, ST_GeometryFromText(:geom,4490), :createUserName, :xspdId, :samplingPointName, :enclosureVideo, :enclosureAudio, :enclosureAlbum, " +
                ":checkStatus, :checkOpinion, :isDamnificationCount, :repeatDataCode, :type)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(samPoiHouSur);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source)>0;
    }

    @Override
    public List<SamPoiHouSur> getSamPoiDataList(SamPoiHouSurQuaParams queParams) {
        StringBuilder sql = new StringBuilder("select * from xczh_sampling_point_house_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and check_status!='0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        if (!PlatformObjectUtils.isEmpty(queParams.getIntensityDetermination())) {
            whereSql.append(" and intensity_determination = '" + queParams.getIntensityDetermination() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointHouseCode())) {
            whereSql.append(" and sampling_point_house_code like '%" + queParams.getSamplingPointHouseCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCheckStatus())) {
            whereSql.append(" and check_status =  '" + queParams.getCheckStatus() + "'");
        }

        sql.append(whereSql).append(orderSql);
        List<SamPoiHouSur> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamPoiHouSur.class));
        return list;
    }

    @Override
    public String getMaxNum(String str) {
        String sql="select max(sampling_point_house_code) from xczh_sampling_point_house_data where del_flag = '0' and sampling_point_house_code like '%"+str+"%'";
        String maxNum=jdbcTemplate.queryForObject(sql,String.class);
        return maxNum;
    }

    @Override
    public SamPoiHouSur findById(String id) {
        String sql="select * from xczh_sampling_point_house_data where id = ? ";
        SamPoiHouSur samPoiHouSur = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SamPoiHouSur.class), id);
        return samPoiHouSur;
    }

    @Override
    public void check(String id, String checkStatus, String checkOpinion) {
        String sql = "update xczh_sampling_point_house_data set check_status = ? , check_opinion = ? where id = ?";
        jdbcTemplate.update(sql,checkStatus,checkOpinion,id);
    }

    @Override
    public void update(SamPoiHouSur samPoiHouSur) {
        String sql="update xczh_sampling_point_house_data set xspd_id = :xspdId ,sampling_point_name = :samplingPointName ,sampling_point_households = :samplingPointHouseholds ," +
                "per_household_area = :perHouseholdArea ,sampling_point_population = :samplingPointPopulation ,house_typical_damage = :houseTypicalDamage ,death_toll = :deathToll ," +
                "disappear_toll = :disappearToll ,serious_injury_toll = :seriousInjuryToll ,minor_wound_toll = :minorWoundToll ,topographic_features = :topographicFeatures ," +
                "personnel_shock = :personnelShock ,reaction_of_vessels = :reactionOfVessels ,macro_description = :macroDescription ,large_scale_photo = :largeScalePhoto ," +
                "large_scale_video = :largeScaleVideo ,macroscopic_anomaly = :macroscopicAnomaly ,geologic_hazard = :geologicHazard ,building_earthquake_description = :buildingEarthquakeDescription ," +
                "earthquake_description = :earthquakeDescription ,intensity_determination = :intensityDetermination ,disaster_description = :disasterDescription ,before_earthquake_picture = :beforeEarthquakePicture ," +
                "after_earthquake_picture = :afterEarthquakePicture ,uav_aerial_photography_one = :uavAerialPhotographyOne ,uav_aerial_photography_two = :uavAerialPhotographyTwo ,enclosure_photo = :enclosurePhoto ," +
                "enclosure_video = :enclosureVideo ,enclosure_audio = :enclosureAudio ,enclosure_album = :enclosureAlbum ,update_user = :updateUser, update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(samPoiHouSur);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<SamPoiHouSurCheckVo> getSamPoiCheckDataList(SamPoiHouSurQuaParams queParams) {
        StringBuilder sql = new StringBuilder("select * from xczh_sampling_point_house_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and check_status!='0' ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        if (!PlatformObjectUtils.isEmpty(queParams.getIntensityDetermination())) {
            whereSql.append(" and intensity_determination = '" + queParams.getIntensityDetermination() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointHouseCode())) {
            whereSql.append(" and sampling_point_house_code like '%" + queParams.getSamplingPointHouseCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCheckStatus())) {
            whereSql.append(" and check_status =  '" + queParams.getCheckStatus() + "'");
        }

        sql.append(whereSql).append(orderSql);
        List<SamPoiHouSurCheckVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamPoiHouSurCheckVo.class));
        return list;
    }

    @Override
    public void isDamnificationCount(String id,String state) {
        String sql = "update xczh_sampling_point_house_data set is_damnification_count = ? where id = ?";
        jdbcTemplate.update(sql,state,id);
    }

    @Override
    public List<SamPoiHouSur> getTaskListByXsemId(String xsemId) {
        String sql="select * from xczh_sampling_point_house_data where del_flag = ? and xsem_id = ?";
        List<SamPoiHouSur> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamPoiHouSur.class),YNEnum.N.toString(),xsemId);
        return list;
    }

    @Override
    public SamPoiHouSur getTaskListByXspdId(String xspdId) {
        String sql="select * from xczh_sampling_point_house_data where del_flag = ? and xspd_id = ?";
        List<SamPoiHouSur> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamPoiHouSur.class),YNEnum.N.toString(),xspdId);
        return list.size()<1?null:list.get(0);
    }

    @Override
    public ResultParamVo getSamPoiHouSurCalculateByXsemId(String xsemId, List<String> idList) {
        String sql = "SELECT SUM ( CAST (sampling_point_households AS DECIMAL(15, 2)) * CAST(per_household_area AS DECIMAL(15, 2))) AS totalArea," +
                "SUM ( CAST ( sampling_point_population AS DECIMAL ( 15, 2 ) ) ) samplingPointPopulation," +
                "SUM ( CAST ( sampling_point_households AS DECIMAL ( 15, 2 ) ) ) samplingPointHouseholds " +
                "FROM xczh_sampling_point_house_data WHERE del_flag = '"+YNEnum.N.toString()+"' AND per_household_area IS NOT NULL " +
                "AND per_household_area <> '' AND sampling_point_population IS NOT NULL AND sampling_point_population <> '' " +
                "AND per_household_area IS NOT NULL AND per_household_area <> '' AND xsem_id = '"+xsemId+"' AND xspd_id in (:ids)";
        Map idMap = new HashMap();
        idMap.put("ids", idList);
        List query = namedParameterJdbcTemplate.query(sql, idMap, new BeanPropertyRowMapper<>(ResultParamVo.class));
        /*SamPoiHouSur samPoiHouSur = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SamPoiHouSur.class),idMap);*/
        ResultParamVo resultVo = (ResultParamVo) query.get(0);
        return resultVo;
    }

    @Override
    public void submit(String id) {
        String sql = "update xczh_sampling_point_house_data set check_status= ? where id = ?";
        jdbcTemplate.update(sql, "1", id);
    }

    @Override
    public List<SamPoiHouSur> getHouseDataBySurTaskManageId(List<String> xstmIdList) {
        String sql="select * from xczh_sampling_point_house_data where xspd_id in (select id from xczh_sampling_point_data where xstm_id in (:xstmIdList) and  " +
                " del_flag='0' ) and del_flag='0' and check_status ='2'";
        Map idMap = new HashMap();
        idMap.put("xstmIdList", xstmIdList);
        return namedParameterJdbcTemplate.query(sql.toString(), idMap, new BeanPropertyRowMapper<>(SamPoiHouSur.class));
    }

    @Override
    public void updateXsemId(String xsemId, String convertedXsemId) {
        String sql = "update xczh_sampling_point_house_data set xsem_id = '"+xsemId+"'  where xsem_id = '"+convertedXsemId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delete(String[] ids) {
        String sql = "update xczh_sampling_point_house_data set del_flag= ? where id in ('"+String.join("','",ids)+"')";
        jdbcTemplate.update(sql, "1");
    }

    @Override
    public Map<String, Object> getAppPage(SamPoiHouSurQuaParams queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from xczh_sampling_point_house_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getIntensityDetermination())) {
            whereSql.append(" and intensity_determination = '" + queParams.getIntensityDetermination() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointHouseCode())) {
            whereSql.append(" and sampling_point_house_code like '%" + queParams.getSamplingPointHouseCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCheckStatus())) {
            whereSql.append(" and check_status =  '" + queParams.getCheckStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUser())) {
            whereSql.append(" and create_user = '" + queParams.getCreateUser() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamPoiHouSur.class)).size();
        List<SamPoiHouSur> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SamPoiHouSur.class));

        List<SamPoiHouSurVo> samPoiHouSurVoList=new ArrayList<>();
        for(SamPoiHouSur item:list){
            SamPoiHouSurVo samPoiHouSurVo=new SamPoiHouSurVo();
            UpdateUtil.copyNullProperties(item, samPoiHouSurVo);
            if (PlatformObjectUtils.isNotEmpty(item.getBeforeEarthquakePicture())){
                samPoiHouSurVo.setBeforeEarthquakePicture(attachmentInfoService.queryFile(item.getBeforeEarthquakePicture()));
            }
            if (PlatformObjectUtils.isNotEmpty(item.getAfterEarthquakePicture())){
                samPoiHouSurVo.setAfterEarthquakePicture(attachmentInfoService.queryFile(item.getAfterEarthquakePicture()));
            }
            samPoiHouSurVoList.add(samPoiHouSurVo);
        }
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", samPoiHouSurVoList);
        return map;
    }

    @Override
    public List<SamPoiHouSur> getSamPoiHouDataByXsemIdAndType(String xsemId, String type,String checkStatus) {
        StringBuilder sql = new StringBuilder("select *,st_astext(geom) geom from  xczh_sampling_point_house_data  ");
        StringBuilder whereSql = new StringBuilder("where del_flag='0' and check_status !='0' ");
        if (!PlatformObjectUtils.isEmpty(xsemId)) {
            whereSql.append(" and xsem_id = '" + xsemId + "'");
        }
        if (!PlatformObjectUtils.isEmpty(type)) {
            whereSql.append(" and type = '" + type + "'");
        }
        if (!PlatformObjectUtils.isEmpty(checkStatus)) {
            String[] split = checkStatus.split(",");
            List<String> status = Arrays.asList(split);
            whereSql.append(" and check_status in  ('" + String.join("','", status) + "')");
        }


        List<SamPoiHouSur> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(SamPoiHouSur.class));
        return list;
    }

    @Override
    public List<SamPoiHouSur> getHouseTypicalDamage(String xsemId) {
        String sql="select xsem_id,intensity_determination,house_typical_damage from xczh_sampling_point_house_data " +
                "where xsem_id=:xsemId and house_typical_damage is not null and del_flag=:delFlag";
        Map idMap = new HashMap();
        idMap.put("xsemId", xsemId);
        idMap.put("delFlag", YNEnum.N.toString());

        return namedParameterJdbcTemplate.query(sql.toString(), idMap, new BeanPropertyRowMapper<>(SamPoiHouSur.class));

    }
}
